﻿using System;
using System.Data;

namespace Ehai.Web.Framework.Rules
{
    class ExlToaccessRuleHandle : IMsgRuleHandle
    {
        #region IMsgRuleHandle 成员

        public const string Name = "exlToaccess";

        private int _execStatus = 0;
        public int ExecStatus
        {
            get { return _execStatus; }
        }

        /// <summary>
        /// 从excel表中到处数据到access表
        /// </summary>
        /// <param name="p">
        /// 0:(必选)处理类型<br/>
        /// 1:(必选)excel路径<br/>
        /// 2:(必选)access表名<br/>
        /// 3:(必选)sheet名<br/>
        /// 4:(必选)列名<br/>
        /// 5:(必选)关键字<br/>
        /// </param>
        /// <param name="ci">环境变量</param>
        /// <example>
        /// </example>
        /// <exception cref="Ehai.Web.Framework.WebException">
        /// Ehai.Web.Framework.WebException:读取文件错误！
        /// </exception>
        /// <returns>返回access数据</returns>
        public string[][] Execute(string[] p, ContextInfo ci)
        {
            if (p.Length < 6) 
            {
#if DEBUG
                // 操作失败,状态值为-2
                _execStatus = -2;
#endif  
                return null;
            }
            if (p[1].Trim() == String.Empty)
            {
#if DEBUG
                // 操作失败,状态值为1
                _execStatus = 1;
#endif
                return null;
            }

            if (p[2].Trim() == String.Empty)
            {
#if DEBUG
                // 操作失败,状态值为2
                _execStatus = 2;
#endif
                return null;
            }

            string xlsPath = p[1];
            string sheetName = (p[3].Trim() == String.Empty) ? "Sheet1$" : p[3].Trim();
            string acssql = "";
            string sql = "";
            string keyValue = "";
            string values;

            String stracsCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + xlsPath + ";extended Properties=Excel 8.0";//连接excel
            String strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\\ehai2009实践\\8.7\\测试\\Database1.mdb";          //连接access 

            try
            {
                //从excel中读取表的内容
                acssql = "select * from [" + sheetName + "]";
                ApplicationDB acsadb = new ApplicationDB();
                acsadb.ConString = stracsCon;   //定义连接字符串
                acsadb.Sql = acssql;            //sql语句
                DataSet ds = acsadb.GetDataSet();

                //用于操作access数据库
                ApplicationDB adb = new ApplicationDB();
                adb.ConString = strCon;         //定义连接字符串

                //excel中一定要有主键ID，初始化定义为汉字时，才可以更新为汉字，否则更新后不显示
                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    values = "";
                    foreach (DataColumn column in ds.Tables[0].Columns)
                    {
                        values += "'" + row[column] + "',";
                    }
                    values = values.Remove(values.Length - 1);
                    if (p[5].Trim() != String.Empty)//有关键字
                    {
                        if (row[p[5]].ToString() != String.Empty)
                        {
                            keyValue = (string)row[p[5]].ToString();
                            sql = "select * from " + p[2] + " where cstr(" + p[5] + ")='" + keyValue + "'";

                            adb.Sql = sql;
                            DataSet aff = adb.GetDataSet();
                            if (aff.Tables[0].Rows.Count == 0)   //没有此记录则插入
                            {
                                //throw new Exception( ;
                                
                                if (p[4].Trim() == String.Empty) //全部列
                                    sql = "insert into " + p[2] + " values(" + values + ")";
                                else
                                    sql = "insert into " + p[2] + "(" + p[4] + ") values(" + values + ")";
                                adb.Sql = sql;
                                adb.ExecuteNonQuery();
                            }
                            else//有此记录则更新
                            {
                                string expression = "";
                                string[] cols = p[4].Split(',');
                                string[] value = values.Split(',');
                                if (cols.Length == value.Length)
                                {
                                    for (int i = 0; i < cols.Length; i++)
                                    {
                                        if (cols[i] != p[5])
                                            expression += cols[i] + "=" + value[i] + ",";
                                    }
                                    expression = expression.Remove(expression.Length - 1);
                                    sql = "update " + p[2] + " set " + expression + " where cstr(" + p[5] + ")='" + keyValue + "'";
                                    adb.Sql = sql;
                                    adb.ExecuteNonQuery();
                                }
                            }
                        }
                    }
                    else
                    {
                        if (p[4].Trim() == String.Empty)   //全部列
                            sql = "insert into " + p[2] + " values(" + values + ")";
                        else
                            sql = "insert into " + p[2] + "(" + p[4] + ") values(" + values + ")";
                        adb.Sql = sql;                     //acsSql = new EhaiSQL(sql);
                        adb.ExecuteNonQuery();             //替换acsSql.ExecuteNonQuery();
                    }
                }
#if DEBUG
                // 操作成功,状态值为0
                _execStatus = 0;
#endif
                return new string[1][] { new string[] { "success" } };
            }
            catch (Exception ex)
            {
#if DEBUG
                // 操作失败并捕获异常,状态值为-1
                _execStatus = -1;
#endif
                throw new Ehai.Web.Framework.WebException("Ehai.WebFramework.ExlToaccessRule", "导入数据错误！", ex);
            }
        }

        public string GetHelpInfo()
        {
            return  "excel数据导入access";
        }

        public string GetHelpDetail()
        {
            return  "从excel表中导出数据到access表:<br />" +
                    "例: { exlToaccess, D:\\ehai2009实践\\7.29\\Group4.xls, table1, Sheet1$, ID,nickname,tel,bog,state,sta, ID }; <br /> " +
                    "返回:access数据";
        }

        public string[] GetParametersInfo()
        {
            string[] t = new string[6];
            t[0] = "(必选)处理类型,即\"exlToaccess\"";
            t[1] = "(必选)excel路径";
            t[2] = "(必选)access表名";
            t[3] = "(必选)sheet名";
            t[4] = "(必选)关键字";
            t[5] = "(必选)列名";
            return t;

        }

        #endregion
    }
}
